package com.youlai.boot.modules.tp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.youlai.boot.modules.tp.entity.ProductSales;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 商品销售数据Mapper接口
 * 
 * @author Jason
 * @since 2025-06-04
 */
@Mapper
@org.springframework.stereotype.Repository("tpProductSalesMapper")
public interface ProductSalesMapper extends BaseMapper<ProductSales> {

    /**
     * 获取品类销售占比数据
     * 
     * @param timePeriod 时间周期
     * @return 品类销售数据
     */
    @Select("SELECT " +
            "first_category as name, " +
            "SUM(transaction_amount) as amount, " +
            "ROUND(SUM(transaction_amount) * 100.0 / (SELECT SUM(transaction_amount) FROM product_sales WHERE time_period = #{timePeriod} AND is_deleted = 0), 2) as percentage " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0 " +
            "GROUP BY first_category " +
            "ORDER BY amount DESC")
    List<Map<String, Object>> getCategorySalesData(@Param("timePeriod") String timePeriod);

    /**
     * 获取访客和浏览数据
     * 
     * @param timePeriod 时间周期
     * @return 访客浏览数据
     */
    @Select("SELECT " +
            "SUM(visitor_count) as totalVisitors, " +
            "SUM(page_views) as totalPageViews, " +
            "AVG(avg_page_views_per_visitor) as avgPageViewsPerVisitor, " +
            "AVG(avg_stay_time) as avgStayTime " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0")
    Map<String, Object> getVisitorData(@Param("timePeriod") String timePeriod);

    /**
     * 获取成交用户和转化率数据
     * 
     * @param timePeriod 时间周期
     * @return 成交转化数据
     */
    @Select("SELECT " +
            "SUM(transaction_user_count) as totalBuyers, " +
            "SUM(transaction_order_count) as totalOrders, " +
            "SUM(transaction_item_count) as totalItems, " +
            "AVG(conversion_rate) as avgConversionRate, " +
            "AVG(average_transaction_value) as avgTransactionValue " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0")
    Map<String, Object> getTransactionData(@Param("timePeriod") String timePeriod);

    /**
     * 获取品牌销售排行
     * 
     * @param timePeriod 时间周期
     * @param limit 限制数量
     * @return 品牌销售排行
     */
    @Select("SELECT " +
            "brand, " +
            "SUM(transaction_amount) as totalAmount, " +
            "SUM(transaction_order_count) as totalOrders, " +
            "AVG(conversion_rate) as avgConversionRate " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0 " +
            "GROUP BY brand " +
            "ORDER BY totalAmount DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getBrandSalesRanking(@Param("timePeriod") String timePeriod, @Param("limit") int limit);

    /**
     * 获取店铺销售数据
     * 
     * @param timePeriod 时间周期
     * @return 店铺销售数据
     */
    @Select("SELECT " +
            "shop_short_name, " +
            "SUM(transaction_amount) as totalAmount, " +
            "SUM(visitor_count) as totalVisitors, " +
            "AVG(conversion_rate) as avgConversionRate, " +
            "COUNT(DISTINCT brand) as brandCount " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0 " +
            "GROUP BY shop_short_name " +
            "ORDER BY totalAmount DESC")
    List<Map<String, Object>> getShopSalesData(@Param("timePeriod") String timePeriod);

    /**
     * 获取商品浏览量统计
     * 
     * @param timePeriod 时间周期
     * @return 商品浏览统计
     */
    @Select("SELECT " +
            "COUNT(*) as totalProducts, " +
            "SUM(page_views) as totalPageViews, " +
            "AVG(page_views) as avgPageViews, " +
            "MAX(page_views) as maxPageViews " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0")
    Map<String, Object> getProductViewStats(@Param("timePeriod") String timePeriod);

    /**
     * 获取店铺关注数据
     * 
     * @param timePeriod 时间周期
     * @return 店铺关注数据
     */
    @Select("SELECT " +
            "shop_short_name, " +
            "SUM(visitor_count) as followers, " +
            "AVG(avg_stay_time) as avgStayTime " +
            "FROM product_sales " +
            "WHERE time_period = #{timePeriod} AND is_deleted = 0 " +
            "GROUP BY shop_short_name " +
            "ORDER BY followers DESC")
    List<Map<String, Object>> getStoreFollowData(@Param("timePeriod") String timePeriod);
} 